package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

import org.junit.Test;

public class Main {

	
	@Test
	public void test1() throws ClassNotFoundException, SQLException
	{
		String sql=" select * from student ";
		ConnectionUtil connutil=new ConnectionUtil();
		Connection conn=connutil.getConnection();
		
		StatementUtil smutil=new StatementUtil();
		Statement sm=smutil.getStatement(conn);
		
		ResultSetUtil rsutil=new ResultSetUtil();
		ResultSet rs=rsutil.getResultSet(sm, sql);
		
		while (rs.next())
		{
			System.out.println(rs.getInt(1)+"\t"+rs.getString(2));
		}
		conn.close();
		sm.close();
		rs.close();
		
	}
	
	/**
	 * 用户登陆 1,statement
	 * 1：表示登陆成功
	 * 0：表示失败，不存在此账户
	 * @throws SQLException 
	 * @throws ClassNotFoundException 
	 */
	@Test
	public void login1() throws ClassNotFoundException, SQLException
	{
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入用户名：");
		String username = sc.nextLine();
		System.out.println("请输入密码：");
		String pwd = sc.nextLine();
		String sql=" select identifiyuser('"+username+"','"+pwd+"')" ;
		ConnectionUtil connutil=new ConnectionUtil();
		Connection conn=connutil.getConnection();
		
		PreparedStatementUtil psmutil= new PreparedStatementUtil();
		PreparedStatement psm=psmutil.getPreparedStatement(conn, sql); 
		
		StatementUtil smutil=new StatementUtil();
		Statement sm=smutil.getStatement(conn);
		
		ResultSetUtil rsutil=new ResultSetUtil();
		ResultSet rs=rsutil.getResultSet(sm, sql);
		while (rs.next())
		{
			//System.out.println(rs.getInt(1));
			if( rs.getInt(1) == 1 )
			{
				System.out.println("登陆成功");
			}
			else{
				System.out.println("用户不存在");
			}
		}
		
	}
	
	
	
	/**
	 * 用户登陆 2,PreparedStatement
	 */
	@Test
	public void login2() throws ClassNotFoundException, SQLException
	{
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入用户名：");
		String username = sc.nextLine();
		System.out.println("请输入密码：");
		String pwd = sc.nextLine();
		String sql=" select identifiyuser(?,?)" ;
		ConnectionUtil connutil=new ConnectionUtil();
		Connection conn=connutil.getConnection();		
		PreparedStatement psm=conn.prepareStatement(sql);
		psm.setString(1, username);
		psm.setString(2, pwd);
		ResultSet rs=psm.executeQuery();
		while (rs.next())
		{
			//System.out.println(rs.getInt(1));
			if( rs.getInt(1) == 1 )
			{
				System.out.println("登陆成功");
			}
			else{
				System.out.println("用户不存在");
			}
		}	
	}
	
	
	@Test
	public void test2() throws ClassNotFoundException, SQLException
	{
	
		Scanner sc=new Scanner(System.in);
		System.out.println("请输入您的学号：");
		String num=sc.nextLine();
		int id=Integer.parseInt(num);
		System.out.println("请输入您的姓名：");
		String name = sc.nextLine();
		ConnectionUtil connutil=new ConnectionUtil();
		Connection conn=connutil.getConnection();	
		
		//每次都要清空多选表中的答案，方便判断下一个多选题
		String sql1="TRUNCATE TABLE duoxuan;";
		PreparedStatement psm1=conn.prepareStatement(sql1);
		psm1.executeUpdate();
		
		String sq="TRUNCATE TABLE studentanswer;";
		psm1=conn.prepareStatement(sq);
		psm1.executeUpdate();
		
		for(int i=1;i<5;i++)
		{
			String sql=" INSERT into studentanswer (studentid,answerid,studentanswer) VALUE(?,?,?)";
			PreparedStatement psm=conn.prepareStatement(sql);
			psm.setInt(1, id);
			psm.setInt(2, i);
			System.out.println("第"+i+"题，请输入你的答案：");
			String answer=sc.nextLine();
			psm.setString(3, answer);
			psm.executeUpdate();
			
		}
		System.out.println("你已提交答案！");
		
		//将学生多选的答案拆分后存入多选表中
		String sql2="SELECT pr_add(LENGTH(sa.studentanswer)) FROM studentanswer sa WHERE sa.answerid=3;" ;
		psm1=conn.prepareStatement(sql2);
		psm1.executeQuery();
		
		//查看每题的得分情况和正确答案
		System.out.println("你的得分情况为：");
		String sql3="SELECT v1.studentid,a.id,v1.score,a.answer FROM ls_v_v1 v1 left JOIN answer a ON v1.answerid= a.id" ;
		psm1=conn.prepareStatement(sql3);
		ResultSet rs=psm1.executeQuery();
		while(rs.next())
		{
			System.out.println(rs.getInt(1)+"\t"+rs.getInt(2)+"\t"+rs.getInt(3)+"\t"+rs.getString(4));
		}
			
	}
	
	
	
}
